﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace SponsorshipManager.DataAccess
{
    public class ProviderSqlServer:IDbProvider
    {
        public bool PersistConnections { get; set; }

        private SqlConnection cnn
        {
            get;
            set;
        }

        public string ConnectionString
        {
            set; get;
        }



        private SqlConnection GetCnn()
        {
            string cnnString = ConnectionString;
            if(String.IsNullOrEmpty(cnnString)){
                throw new FormatException("Connection string missing. Verify the Config for ConnectionString");
            }
            return new SqlConnection(cnnString);
        }


        private void AddParamsToCmd(List<IDataParameter> Params , string Token, SqlCommand cmd )
        {
            bool AddTokenParameter;
            if (Params == null)
            {
                AddTokenParameter = true;
            }
            else
            {
                foreach (SqlParameter param in Params)
                {
                    if (!(param.ParameterName.Substring(0, 1) == "@"))
                    {
                        param.ParameterName = "@" + param.ParameterName;
                    }
                    if (param.Direction == ParameterDirection.Output && param.DbType == DbType.String)
                    {
                        param.Size = 4000;
                    }
                    cmd.Parameters.Add(param);
                }
                AddTokenParameter = (Params.Find(param => param.ParameterName == "@Token") == null);
            }

            if (AddTokenParameter)
            {
                cmd.Parameters.Add(TokenParameter(Token));
            }

            cmd.Parameters.Add(ReturnParameter());

        }

        private SqlParameter TokenParameter(string Token)
        {

            SqlParameter param = new SqlParameter();
            param.DbType=DbType.String;
            param.ParameterName="@Token";
            if (Token != "")
            {
                param.Value = Token;
            }
            param.Direction=ParameterDirection.Input;
            return param;
        }

        private SqlParameter ReturnParameter()
        {
            SqlParameter param = new SqlParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "@Return_Value";
            param.Direction = ParameterDirection.ReturnValue;
            return param;
        }

        public int ExecStoredProc(string spName, List<System.Data.IDataParameter> Params, string Token)
        {
            SqlCommand cmd = PrepareCommand(spName,Params,Token);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Stored Procedure " + spName + " not excecuted correctly.", e);
            }
            int ret = (int) cmd.Parameters["@Return_Value"].Value;
            cmd.Parameters.Clear();
            CleanParams(Params);
            return ret;
        }

        public int ExecStoredProc(string spName, List<System.Data.IDataParameter> Params, out DataTable Result, string Token)
        {
            SqlCommand cmd = PrepareCommand(spName, Params, Token);
            try
            {
                Result = new DataTable();
                Result.Load(cmd.ExecuteReader());
            }
            catch (Exception e)
            {
                throw new Exception("Stored Procedure " + spName + " not excecuted correctly.", e);
            }
            int ret = (int)cmd.Parameters["@Return_Value"].Value;
            cmd.Parameters.Clear();
            CleanParams(Params);
            return ret;
        }


        private SqlCommand PrepareCommand(string spName, List<System.Data.IDataParameter> Params, string Token)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cnn;
            cmd.CommandText = spName;
            AddParamsToCmd(Params, Token, cmd);
            return cmd;
        }
        
        private void CleanParams( List<System.Data.IDataParameter> Params)
        {
            if(Params!=null){
                foreach(SqlParameter param in Params){
                    param.ParameterName = param.ParameterName.Replace("@", "");
                }
            }
        }


        public Boolean Connected {
            get 
            {
                if (cnn == null)
                {
                    return false;
                }
                else
                {
                    return (cnn.State == ConnectionState.Open);
                }
            }
            set
            {
                if (value == true)
                {
                    Connect();
                }
                else
                {
                    Disconnect();
                }
            }
        }

        private void Connect()
        {
            if(cnn==null)
            {
                cnn = new SqlConnection(ConnectionString);
            }
            
            if(cnn.State==ConnectionState.Closed || 
               cnn.State==ConnectionState.Broken)
            {
               try
               {
                   cnn.Open();
                }
                catch(Exception e)
                {
                    throw new Exception("Database not opening correctly", e);
                }
            }
            while( cnn.State!=ConnectionState.Closed && 
                   cnn.State!=ConnectionState.Broken && 
                   cnn.State!=ConnectionState.Open)
            {
                System.Threading.Thread.Sleep(500);
            }

        }

        private void Disconnect() {
            cnn.Close();

            //throw new NotImplementedException();
        }
        public IDataAdapter DataAdapter()
        {
            throw new NotImplementedException();
        }

        public IDataReader DataReader()
        {
            throw new NotImplementedException();
        }

        public IDataParameter Parameter()
        {
            return (IDataParameter) new SqlParameter();
        }


    }
}
